09. Text + Quiz: JOIN Revisited

JOIN Revisited

Let's look back at the first JOIN you wrote.

SELECT orders.*
FROM orders
JOIN accounts
ON orders.account_id = accounts.id;

Here is the ERD for these two tables:

Notice

Notice our SQL query has the two tables we would like to join - one in the FROM and the other in the JOIN. Then in the ON, we will ALWAYs have the PK equal to the FK:

The way we join any two tables is in this way: linking the PK and FK (generally in an ON statement).

Practice

Use the image above to assist you. If we wanted to join the sales_reps and region tables together, how would you do it
SOLUTION: ON sales_reps.region_id = region.id

JOIN More than Two Tables

This same logic can actually assist in joining more than two tables together. Look at the three tables below.

The Code

If we wanted to join all three of these tables, we could use the same logic. The code below pulls all of the data from all of the joined tables.

SELECT *
FROM web_events
JOIN accounts
ON web_events.account_id = accounts.id
JOIN orders
ON accounts.id = orders.account_id

Alternatively, we can create a SELECT statement that could pull specific columns from any of the three tables. Again, our JOIN holds a table, and ON is a link for our PK to equal the FK.

To pull specific columns, the SELECT statement will need to specify the table that you are wishing to pull the column from, as well as the column name. We could pull only three columns in the above by changing the select statement to the below, but maintaining the rest of the JOIN information:

SELECT web_events.channel, accounts.name, orders.total

We could continue this same process to link all of the tables if we wanted. For efficiency reasons, we probably don't want to do this unless we actually need information from all of the tables.

Next Concept